<?php
//close your day poll, lock topic, add order and send emails to winners
//run at 00:05 on Wednesday, Friday and Sunday
$query=$this->db->query("SELECT name_id, name, forum_id, topic_id, topic_first_post_id FROM " . DB_PREFIX . "community_yourday WHERE used='0' AND being_used='1' AND being_polled='1' AND being_displayed='1' ORDER BY upcoming_date ASC LIMIT 0,1");

if($query->row){
    echo 'end your day poll cron run!';

    //update your day name as used
    $this->db->query("UPDATE " . DB_PREFIX . "community_yourday SET being_polled='0', used='1', being_used='0', being_displayed='0' WHERE name_id='" . $query->row['name_id'] . "'");

    //lock topic
    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_status='1', topic_category_id='zzzz' WHERE topic_id='" . (int)$query->row['topic_id'] . "'");

    //get winners of the poll
    $winner_query=$this->db->query("SELECT p.poll_option_id AS winner_id, y.username FROM " . DB_PREFIX . "community_poll_options AS p LEFT JOIN " . DB_PREFIX . " community_yourday_participant AS y ON (y.user_id=p.poll_option_id) WHERE p.topic_id='" . (int)$query->row['topic_id'] . "' ORDER BY p.poll_option_total DESC LIMIT 0,5");

    $winner_str='';
    $winner_id_str='';

    foreach($winner_query->rows as $winner){
        $winner_str.=$winner['username'] . ', ';
        $winner_id_str.=$winner['winner_id'] . ',';
    }

    $winner_str=rtrim($winner_str,', ');
    $winner_str=trim($winner_str);

    $winner_id_str=rtrim($winner_id_str,',');

    //update post content
    $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_text='" . $this->db->escape('Hi, guys. Thank you for your votes. The poll has ended and winners for ' . ucwords($query->row['name']) . '\'s Day are: ' . $winner_str . '.') . "' WHERE post_id='" . (int)$query->row['topic_first_post_id'] . "'");

    //get winner data
    $participant_query=$this->db->query("SELECT * FROM " . DB_PREFIX . "community_yourday_participant WHERE name_id='" . $query->row['name_id'] . "' AND user_id IN (" . $winner_id_str . ")");


    echo '<pre>';
    print_r($participant_query->rows);
    echo '</pre>';

    //add orders and send emails to winners
    foreach($participant_query->rows as $participant){
        //get customer address
        $address_query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "address WHERE address_id = '" . (int)$participant['address_id'] . "' AND customer_id = '" . (int)$participant['customer_id'] . "'");

        $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$address_query->row['country_id'] . "'");

        if ($country_query->num_rows) {
            $country = $country_query->row['name'];
            $iso_code_2 = $country_query->row['iso_code_2'];
            $iso_code_3 = $country_query->row['iso_code_3'];
            $address_format = $country_query->row['address_format'];
        } else {
            $country = '';
            $iso_code_2 = '';
            $iso_code_3 = '';
            $address_format = '';
        }

        $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$address_query->row['zone_id'] . "'");

        if ($zone_query->num_rows) {
            $zone = $zone_query->row['name'];
            $code = $zone_query->row['code'];
        } else {
            $zone = '';
            $code = '';
        }

        $address_data = array(
            'firstname'      => $address_query->row['firstname'],
            'lastname'       => $address_query->row['lastname'],
            'company'        => $address_query->row['company'],
            'address_1'      => $address_query->row['address_1'],
            'address_2'      => $address_query->row['address_2'],
            'postcode'       => $address_query->row['postcode'],
            'city'           => $address_query->row['city'],
            'zone_id'        => $address_query->row['zone_id'],
            'zone'           => $zone,
            'zone_code'      => $code,
            'country_id'     => $address_query->row['country_id'],
            'country'        => $country,
            'iso_code_2'     => $iso_code_2,
            'iso_code_3'     => $iso_code_3,
            'address_format' => $address_format
        );

        //add order
        $this->db->query("INSERT INTO `" . DB_PREFIX . "order` SET invoice_prefix = '', store_id = '0', store_name = 'Micbus.com', store_url = '" . $this->db->escape('http://www.micbus.com') . "', customer_id = '" . (int)$participant['customer_id'] . "', customer_group_id = '" . (int)$participant['customer_group_id'] . "', firstname = '" . $this->db->escape($address_data['firstname']) . "', lastname = '" . $this->db->escape($address_data['lastname']) . "', email = '" . $this->db->escape($participant['user_email']) . "', telephone = '', fax = '', payment_firstname = '" . $this->db->escape($address_data['firstname']) . "', payment_lastname = '" . $this->db->escape($address_data['lastname']) . "', payment_company = '" . $this->db->escape($address_data['company']) . "', payment_address_1 = '" . $this->db->escape($address_data['address_1']) . "', payment_address_2 = '" . $this->db->escape($address_data['address_2']) . "', payment_city = '" . $this->db->escape($address_data['city']) . "', payment_postcode = '" . $this->db->escape($address_data['postcode']) . "', payment_country = '" . $this->db->escape($address_data['country']) . "', payment_country_id = '" . (int)$address_data['country_id'] . "', payment_zone = '" . $this->db->escape($address_data['zone']) . "', payment_zone_id = '" . (int)$address_data['zone_id'] . "', payment_address_format = '', payment_code = '" . $this->db->escape('free_checkout') . "', payment_method = '" . $this->db->escape('Free Checkout') . "', language_id = '1', currency_id = '2', currency_code = 'USD', comment='" . $this->db->escape(ucwords($query->row['name'])) . " Day', currency_value = '1', order_status_id='1', date_added = NOW(), date_modified = NOW()");

        $order_id = $this->db->getLastId();

        $this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '613', name = '" . $this->db->escape('$20 Worth of Free Sample - (A TF Card Needed To Player Music) TF / Micro SD Card Reader Clip MP3 Player - 5 Samples Per Day') . "', model = '" . $this->db->escape('F0001') . "', quantity = '1', price = ' 0.0000', total = '0', tax = '0', reward = '0'");


        $this->db->query("INSERT INTO " . DB_PREFIX . "order_total SET order_id = '" . (int)$order_id . "', code = '" . $this->db->escape('sub_total') . "', title = '" . $this->db->escape('Sub-Total') . "', text = '" . $this->db->escape('$0.00') . "', `value` = '0', sort_order = '1'");

        $this->db->query("INSERT INTO " . DB_PREFIX . "order_total SET order_id = '" . (int)$order_id . "', code = '" . $this->db->escape('total') . "', title = '" . $this->db->escape('Total') . "', text = '" . $this->db->escape('$0.00') . "', `value` = '0', sort_order = '2'");

        //send emails
        $subject = 'Congratulations! You Have Won The Freebie For' . ucwords($query->row['name']) . '\'s Day!';

        $message = "You defeat other participants in the poll for " . ucwords($query->row['name']) . "'s Day and win the freebie now! An order for the freebie has been added into your account and you can check it out at:\n";
        $message .= $this->url->link('account/order') . "\n\n";
        $message .= "Cheers\n";
        $message .= $this->config->get('config_name') . "\40Community Manager Team";

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($participant['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40Community Manager Team");
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();

        sleep(0.1);
    }

    //get loser data and send emails to losers
    $loser_query=$this->db->query("SELECT * FROM " . DB_PREFIX . "community_yourday_participant WHERE name_id='" . $query->row['name_id'] . "' AND user_id NOT IN (" . $winner_id_str . ")");

    $mail_num=0;

    foreach($loser_query->rows as $loser){
        if($mail_num==499){
            $subject =  'Emails sent to It\'s Your Day losers reach 499!';

            $message = 'Time is:' . date('Y-m-d H:i:s') . "\n";

            $mail = new Mail();
            $mail->protocol = $this->config->get('config_mail_protocol');
            $mail->parameter = $this->config->get('config_mail_parameter');
            $mail->hostname = $this->config->get('config_smtp_host');
            $mail->username = $this->config->get('config_smtp_username');
            $mail->password = $this->config->get('config_smtp_password');
            $mail->port = $this->config->get('config_smtp_port');
            $mail->timeout = $this->config->get('config_smtp_timeout');
            $mail->setTo('marcelwu@126.com');
            $mail->setFrom($this->config->get('config_community_email'));
            $mail->setSender($this->config->get('config_name') . "\40Community Emergency Team");
            $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
            $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
            $mail->send();

            break;
        }

        //send emails
        $subject = 'Oops! You Have Lost The Freebie For' . ucwords($query->row['name']) . '\'s Day.';

        $message = "We are sorry but you lose the poll for " . ucwords($query->row['name']) . "'s Day and lose the freebie because of insufficient votes. But it is gonna be ok. Why not check other not-so-competitive contests in our forum :) Check them out at:\n";
        $message .= $this->url->link('community/community') . "\n\n";
        $message .= "Cheers\n";
        $message .= $this->config->get('config_name') . "\40Community Manager Team";

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($loser['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40Community Manager Team");
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();

        $mail_num++;
        sleep(0.1);
    }

    //clear sql cache
    $this->sql_cache->delete('viewtopic.posts.' . md5($query->row['topic_id']));
    $this->sql_cache->delete('viewforum.topics.' . md5($query->row['forum_id']));
}

//clear your name cache
$this->sql_cache->delete('community.upcomingnames');
?>